IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptMetric_PCROAwardsToBeActivatedSeparateDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptMetric_PCROAwardsToBeActivatedSeparateDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
-- csp_rptMetric_PCROAwardsToBeActivatedSeparateDetails 'WEEK','12/28/2008',0,'12/28/2009',0,NULL,NULL,NULL, 41359
CREATE  PROCEDURE [dbo].csp_rptMetric_PCROAwardsToBeActivatedSeparateDetails
(    
  	@PeriodType varchar(50),
 	@PeriodStartDate smalldatetime,
	@Institution INT = 0,
	--new parameters for metrics details breakdown report    
 	@PeriodEndDate smalldatetime = NULL,
 	@Department INT = 0,
 	@Unit varchar(1000) = '0',
 	@PrincipalInvestigator varchar(50)='0',
 	@Sponsor  varchar(50) = '0', 
 	@DomainUserId INT
)    
AS    
BEGIN
 
	SELECT paba.PeriodType,
			paba.PeriodStartDate,
			ai.Institution, 
			ai.Department,
			paba.ProposalNumber,
			ai.PrincipalInvestigator  AS PIName, 
			paba.ChiefCode,
			ai.SponsorName AS Sponsor, 
			ai.SponsorAgreementId as SponsorId,
			paba.ProposalStatus,
			paba.ExecutedDate,
			paba.UploadType,
			paba.UploadDate,
			paba.UploadedBy,
			paba.CoversheetUploadDate,
			paba.CoversheetUploadedBy,
			paba.PostAwardManager,
			paba.LastActivityComment,
			paba.LastActivityDate,
			paba.LastActivityEnterer
	FROM  rptMetric_PCROAwardsToBeActivated  paba
	INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec
		ON (sec.FolderNumber = paba.ProposalNumber)
	INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
		ON ai.FolderNumber = paba.ProposalNumber
	WHERE	paba.PeriodType = @PeriodType
	AND paba.PeriodStartDate BETWEEN @PeriodStartDate AND @PeriodEndDate
	AND	(ai.InstitutionId = @Institution OR @Institution = 0 OR @Institution IS NULL)
	AND (ai.DepartmentId = @Department OR @Department = 0 OR @Department is NULL)
	AND (ai.UnitId in (Select * from dbo.fnSplitFundNumbers(@Unit)) OR @Unit='0' OR @Unit='' OR @Unit is null)    
	AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator = '' OR @PrincipalInvestigator = '0')  
	AND (ai.SponsorId = @Sponsor OR @Sponsor IS NULL OR @Sponsor = '' OR @Sponsor = '0')

END 

GO

